package inapier.utility;

import java.io.Serializable;
import java.util.Calendar;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class mySQLite implements Serializable{
	
	private static final long serialVersionUID = 9218751960110601623L;
	
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	
	final String [] faqFields = new String[] {"idx", "saction", "number",
			"title", "question", "answer"};
	
	final String [] supportFields = new String[] {"idx", "title", "contents", //2
			"s_update", "income", "your_age", "town", "is_childrun", "is_married", //8
			"is_pregnanted", "is_deliveried", "is_cared", "is_foreigner", "is_disabled", //13
			"is_parent", "is_sterility", "is_single", "is_breast_feeding", "place", //18
			"department", "phone", "homepage"}; //21
	
	final String [] supportPlace = new String[] {"idx", "p_name", "address", "latitude",
			"longitude", "phone"};
	
	final String [] favorite = new String[] {"idx", "support_idx", "is_completed",
			"r_date", "u_date"};
	
	final String DBPath = "/sdcard/data/inapier.agalove/databases/";

	/**
	 * Database creation sql statement
	 */
//	private static final String DATABASE_CREATE = "create table notes (_id integer primary key autoincrement, "
//			+ "title text not null, body text not null);";

	private static String DATABASE_NAME ="";
	private static String DATABASE_TABLE ="";
	private static final int DATABASE_VERSION = 2;

	private final Context mCtx;

	private class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

//			db.execSQL(DATABASE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
//					+ newVersion + ", which will destroy all old data");
//			db.execSQL("DROP TABLE IF EXISTS notes");
//			onCreate(db);
		}
	}

	public mySQLite(Context ctx,String dbName, String tableName) {
		DATABASE_NAME = dbName;
		DATABASE_TABLE = tableName;
		this.mCtx = ctx;
	}

	public mySQLite open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public long insert(String[][] values) {
		ContentValues initialValues = new ContentValues();
		for(int i=0; i<values.length; i++){
			initialValues.put(values[i][0], values[i][1]);
		}

		return mDb.insert(DATABASE_TABLE, null, initialValues);
	}
	
	public long insertFavorite(int supportIdx) {
		ContentValues values = new ContentValues();
		
		Calendar a = Calendar.getInstance();
		String date = a.get(a.YEAR) + a.get(a.MONTH) + a.get(a.DAY_OF_MONTH) + " / " +
						a.get(a.HOUR) + a.get(a.MINUTE) + a.get(a.SECOND);
		
		values.put("support_idx", supportIdx);
		values.put("is_completed", 0);
		values.put("r_date", date);
		values.put("u_date", date);
		
		return mDb.insert("favorite", null, values);
	}
	
	
	public boolean favoriteEvent(int supportIdx, boolean state) {
		if(!state){
			Cursor result = getFavorite(supportIdx);	
			if(result.getCount() == 0) {					
				insertFavorite(supportIdx);
				return true;
			}
		}		
		updateFavorite(supportIdx, state);
		return true;			
	}
	
	public boolean deleteFavorite(int idx) {
		return mDb.delete(DATABASE_TABLE, "support_idx=" + idx, null) > 0;
	}
	
	
	public boolean updateFavorite(int supportIdx, boolean state) {
		
		ContentValues args = new ContentValues();
		
		Calendar a = Calendar.getInstance();
		String date = a.get(a.YEAR) +""+ a.get(a.MONTH) +""+ a.get(a.MONTH) + " / " +
						a.get(a.HOUR) +""+ a.get(a.MINUTE) +""+ a.get(a.SECOND);
				
		args.put("is_completed", state?1:0);		
		args.put("u_date", date);		

		return mDb.update("favorite", args, "support_idx=" + supportIdx, null) > 0;
	}
	

	public boolean delete(long rowId) {

		Log.i("Delete called", "value__" + rowId);
		return mDb.delete(DATABASE_TABLE, "idx=" + rowId, null) > 0;
	}


	
	public Cursor fetchAll(String[] fields) {

		return mDb.query(DATABASE_TABLE, fields, null, null, null, null, null);
	}
	

	public Cursor fetch(String[] fields, long rowId) throws SQLException {

		Cursor mCursor = mDb.query(true, DATABASE_TABLE, fields, "idx=" + rowId, null, null, null, null, null);
				
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	public Cursor getFaq(){		
		int rowId = (int) (Math.random() * 20);
		rowId = rowId==0?1:rowId;
		return fetch(faqFields, rowId);
	}
	
	public Cursor getPloicyAll(){		
		return fetchAll(supportFields);
	}
	
	public Cursor getPloicy(int row){		
		return fetch(supportFields, row);
	}
	
	public Cursor getPloicyPlace(int placeIdx){		
		return fetch(supportPlace, placeIdx);
	}
	
	public Cursor getFavorite(int supportIdx){
		Cursor mCursor = mDb.query(true, "favorite", favorite, "support_idx=" + supportIdx, null, null, null, null, null);
		
		if (mCursor != null) {
			mCursor.moveToFirst();
		} else {
			return null;
		}
		return mCursor;
	}
	
	public Cursor getFavoriteAll(){
		return fetchAll(favorite);
	}

	public boolean update(long rowId, String[][] values) {
		ContentValues args = new ContentValues();
		for(int i=0; i<values.length; i++){
			args.put(values[i][0], values[i][1]);
		}

		return mDb.update(DATABASE_TABLE, args, "idx=" + rowId, null) > 0;
	}
}